﻿# coding: UTF-8
# Filename : Mysql.py
# Company : imedia
# Author : rose
import ConfigParser
import MySQLdb
import sys,os
import traceback
import datetime
import threading
import log
reload(sys) 
sys.setdefaultencoding('utf-8')

#读取配置文件
def readFromIni(filePath, key):
    config = ConfigParser.ConfigParser()
    value = ''
    with open(filePath, 'r') as cfgfile:
        config.readfp(cfgfile)
        value = config.get('info', key)
        return value
#获取当前执行文件的绝对路径
linux_file_path = sys.path[0] + os.sep + 'init.ini';
url = readFromIni(linux_file_path,'url').strip()
database = readFromIni(linux_file_path,'database').strip()
username = readFromIni(linux_file_path,'username').strip()
password = readFromIni(linux_file_path,'password').strip()
charset = readFromIni(linux_file_path,'charset').strip()


#获取连接
def getConnection():
    try:
        conn = MySQLdb.Connection(url, username, password, database, use_unicode=True,charset='utf8')
    except:
        print traceback.format_exc()
        log.print_error_log('exception:mysql->method_getConnection_获取连接异常')
    return conn

#执行插入脚本
#sql: 插入语句
#param: 为参数['', '']
def insert(sql, param):
    if sql == None or sql.strip() == '':
        log.print_error_log('warning:mysql.py->method_insert_插入语句为空')
        print '插入语句为空'
        return
    result = 0
    try:
        conn = getConnection()
        curs = conn.cursor()
        result = curs.execute(sql, param)
    except:
        conn.rollback()
        print '数据插入异常'
        log.print_error_log('exception:mysql.py->method_insert_数据插入异常')
        print traceback.format_exc()
    finally:
        conn.commit()
        curs.close()
        conn.close()
    if result > 0:
        return True
    else:
        return False    
#执行批量插入脚本
#sql: 插入语句
#param: 为参数[(),(),()]
def insertMany(sql, param):
    if sql == None or sql.strip() == '':
        log.print_error_log('warning:mysql.py->method_insert_插入语句为空')
        print '插入语句为空'
        return
    result = 0
    try:
        conn = getConnection()
        curs = conn.cursor()
        #执行批量插入
        result = curs.executemany(sql, param)
    except:
        conn.rollback()
        print '数据批量插入异常'
        log.print_error_log('exception:mysql.py->method_insertMany_批量数据插入异常')
        print traceback.format_exc()
    finally:
        conn.commit()
        curs.close()
        conn.close()
    if result > 0 :
        return True
    else:
        return False
#查找某表数据
def doSearch(sql, param):
    if sql == None or sql.strip() == '':
        log.print_error_log('warning:mysql.py->method_doSearch_查询语句为空')
        print '查询语句为空'
        return
    cds = []
    try:
        conn = getConnection()
        curs = conn.cursor()
        curs.execute(sql, param)
        cds = curs.fetchall()
    except:
        print traceback.format_exc()
        print '查询异常'
        log.print_error_log('exception:mysql.py->method_doSearch_查询异常')
    finally:
        curs.close()
        conn.close()
    return cds
#执行删除
def delete(sql):
    if sql == None or sql.strip() == '':
        log.print_error_log('warning:mysql.py->method_delete_删除语句为空')
        print '删除语句为空'
        return
    result = 0
    try:
        conn = getConnection()
        curs = conn.cursor()
        result = curs.execute(sql)
    except:
        conn.rollback()
        print traceback.format_exc()
        log.print_error_log('error:mysql.py->method_delete_删除出现异常')
    finally:
        conn.commit()
        curs.close()
        conn.close()
    if result > 0:
        return True
    else:
        return False
#查找某表数据 示例
def doSearchExample():
    try:
        sql = 'select * from ad_media'
        param = None
        conn = getConnection()
        curs = conn.cursor()
        curs.execute(sql, param)
        cds = curs.fetchall()
        for obj in cds:
            print obj
            print obj[1]
    except:
        print traceback.format_exc()
        print '查询异常'
        log.print_error_log('exception:mysql.py->method_doList_查询异常')
    finally:
        curs.close()
        conn.close()
def test():
    #不需要传参
    #sql = 'insert into goods  (name, price) values ("adsf", "asdf")'
    #insert(sql,None)
    
    #传参数时候写法
    #sql = 'insert into goods  (name, price) values (%s,%s)'
    #insert(sql,['test', 'hello'])
    
    #执行批量插入
    '''c.executemany(
      """INSERT INTO breakfast (name, spam, eggs, sausage, price)
      VALUES (%s, %s, %s, %s, %s)""",
      [
      ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
      ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
      ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
      ] )'''
    
    conn = getConnection()
    sql = 'select * from ad_media'
    conn.query(sql)
    r = conn.store_result()
    print r.num_rows()
    print r.num_fields()
    fields = []
    field_info = r.describe()
    for item in field_info:
        print item
def test2():
    sql = 'select * from ad_media'
    param = None
    result = doSearch(sql, param)
    print '查询结果 %s' % len(result)
    for obj in result:
        print str(obj[0]),str(obj[1]), str(obj[2]), str(obj[3]), str(obj[4]), str(obj[5])
    
    print '----------------------------以上查询-----------------------------------------'
    
    sql = 'insert into mytest (t_name, t_price) values(%s, %s)'
    values = []
    for i in range(6):
        if i == 1:
            values.append(('你好' + str(i), '100' + str(i)))
        else:
            values.append(('你好' + str(i), '100' + str(i)))
    param = values
    insert_state = insertMany(sql, param)
    delete_state = delete('delete from mytest where id in (5)')
    print '插入' ,insert_state
    print '删除' , delete_state
class InsertThread(threading.Thread):
    def __init__(self, cur, sql, param):
        threading.Thread.__init__(self)
        self.cur = cur
        self.param = param
        self.sql = sql
    def run(self):
        print self.sql
        print self.cur
        #注意同步cur线程启动时候 是非线程安全
        self.cur.executemany(self.sql, self.param)
#模拟虚拟数据测试    
def insertTestData():
    conn = getConnection()
    sql = '''insert into mytest (t_name, t_price, t_class, t_date, t_password) values (%s, %s, %s, %s, %s) '''
    cur = conn.cursor()
    param = []
    for i in range(1, 1000000):
        str_i = str(i)
        value = ('test'+ str_i, '10' + str_i, 'class' + str_i, datetime.datetime.now(), 'password' + str_i)
        param.append(value)
        if i % 50000 == 0:
            insertThread = InsertThread(cur, sql, param)
            #开启线程插入
            insertThread.start()
            #cur.executemany(sql, param)
            #初始化数据结构
            param = []
    #cur.executemany(sql, param)
    insertThread = InsertThread(cur, sql, param)
    #开启线程插入
    insertThread.start()

#测试
def doinsertTestData():
    a = datetime.datetime.now()
    insertTestData()
    b = datetime.datetime.now()
    print b - a
def dosearchtest():
    a = datetime.datetime.now()
    sql = ''' select * from mytest where t_class = %s and t_password = %s '''
    rst = doSearch(sql,['class290001', 'password290001'])
    for obj in rst:
        print obj
    b = datetime.datetime.now()
    print b - a
        
if __name__ == '__main__':
    doinsertTestData()
    #dosearchtest()
    #test2()
